﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Configuration;
using MySql.Data.MySqlClient;
using System.Data;
using DFT.Infos;

namespace DFT.DB
{
    public static class DBManager
    {
        private static String GetConnectionString()
        {
            return ConfigurationManager.ConnectionStrings["MySQLConnectionString"].ConnectionString;
        }

        public static Boolean CreateNewUser(String fullName, String email, String pass, ref String returnCode)
        {
            MySqlCommand cmd = new MySqlCommand("CreateNewUser", new MySqlConnection(GetConnectionString()));
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("p_fullName", fullName);
            cmd.Parameters.AddWithValue("p_email", email);
            cmd.Parameters.AddWithValue("p_pass", pass);
            cmd.Parameters.Add("statusCode", MySqlDbType.VarChar).Direction = ParameterDirection.Output;
            try
            {
                cmd.Connection.Open();
                int rowEff = cmd.ExecuteNonQuery();
                returnCode = cmd.Parameters["statusCode"].Value.ToString();
                if (rowEff > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception ex)
            {
                returnCode = ex.Message;
                return false;
            }
            finally
            {
                cmd.Connection.Close();
            }
        }

        public static Boolean ValidateUser(String email, String pass, ref String returnCode, ref UserInfo userInfo)
        {
            MySqlCommand cmd = new MySqlCommand("ValidateUser", new MySqlConnection(GetConnectionString()));
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("p_email", email);
            cmd.Parameters.AddWithValue("p_pass", pass);
            try
            {
                cmd.Connection.Open();
                MySqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                if (dr.HasRows)
                {
                    dr.Read();
                    returnCode = Convert.ToString(dr["statusCode"]);
                    if (returnCode == "0")
                    {
                        userInfo.UserId = Convert.ToInt64(dr["userId"]);
                        userInfo.Email = email;
                        userInfo.FullName = Convert.ToString(dr["fullName"]);
                        userInfo.RegistrationDt = Convert.ToDateTime(dr["registrationDt"]);
                        if (dr["lastlogin"] != DBNull.Value)
                        {
                            userInfo.Lastlogin = Convert.ToDateTime(dr["lastlogin"]);
                        }
                    }
                    else
                    {
                        return false;
                    }
                    dr.Close();
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception ex)
            {
                returnCode = ex.Message;
                return false;
            }
            finally
            {
                cmd.Connection.Close();
            }
        }

        public static Boolean UpdateUserData(Int64 userId, String newFullName, String newEmail, ref String returnCode)
        {
            MySqlCommand cmd = new MySqlCommand("UpdateUserData", new MySqlConnection(GetConnectionString()));
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("p_userId", userId);
            cmd.Parameters.AddWithValue("p_newFullName", newFullName);
            cmd.Parameters.AddWithValue("p_newEmail", newEmail);
            cmd.Parameters.Add("statusCode", MySqlDbType.VarChar).Direction = ParameterDirection.Output;
            try
            {
                cmd.Connection.Open();
                int rowEff = cmd.ExecuteNonQuery();
                returnCode = cmd.Parameters["statusCode"].Value.ToString();
                if (rowEff > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception ex)
            {
                returnCode = ex.Message;
                return false;
            }
            finally
            {
                cmd.Connection.Close();
            }
        }

        public static Boolean ResetPasswordRequest(String email, String newPassword, ref String fullName, ref String returnCode)
        {
            MySqlCommand cmd = new MySqlCommand("ResetPasswordRequest", new MySqlConnection(GetConnectionString()));
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("p_email", email);
            cmd.Parameters.AddWithValue("p_newPassword", newPassword);
            cmd.Parameters.Add("p_fullName", MySqlDbType.VarChar).Direction = ParameterDirection.Output;
            cmd.Parameters.Add("statusCode", MySqlDbType.VarChar).Direction = ParameterDirection.Output;
            try
            {
                cmd.Connection.Open();
                int rowEff = cmd.ExecuteNonQuery();
                returnCode = cmd.Parameters["statusCode"].Value.ToString();
                fullName = cmd.Parameters["p_fullName"].Value.ToString();
                if (rowEff > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception ex)
            {
                returnCode = ex.Message;
                return false;
            }
            finally
            {
                cmd.Connection.Close();
            }
        }

        public static Boolean ChangePasswordRequest(Int64 userId, String oldPassword, String newPassword, ref String returnCode)
        {
            MySqlCommand cmd = new MySqlCommand("ChangePasswordRequest", new MySqlConnection(GetConnectionString()));
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("p_userId", userId);
            cmd.Parameters.AddWithValue("p_oldPassword", oldPassword);
            cmd.Parameters.AddWithValue("p_newPassword", newPassword);
            cmd.Parameters.Add("statusCode", MySqlDbType.VarChar).Direction = ParameterDirection.Output;
            try
            {
                cmd.Connection.Open();
                int rowEff = cmd.ExecuteNonQuery();
                returnCode = cmd.Parameters["statusCode"].Value.ToString();
                if (rowEff > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception ex)
            {
                returnCode = ex.Message;
                return false;
            }
            finally
            {
                cmd.Connection.Close();
            }
        }
        
        public static Boolean GetAllProfilesUser(Int64 userId, ref DataTable profilesTable)
        {
            MySqlCommand cmd = new MySqlCommand("GetAllProfilesUser", new MySqlConnection(GetConnectionString()));
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("p_userId", userId);
            try
            {
                cmd.Connection.Open();
                MySqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

                if (dr.HasRows)
                {
                    profilesTable.Load(dr);
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception ex)
            {
                return false;
                throw ex;
            }
            finally
            {
                cmd.Connection.Close();
            }
        }

        public static Boolean GetImage(Int64 profId, ref Byte[] image, ref String imageType)
        {
            MySqlCommand cmd = new MySqlCommand("GetImage", new MySqlConnection(GetConnectionString()));
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("p_profId", profId);
            try
            {
                cmd.Connection.Open();
                MySqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                if (dr.HasRows)
                {
                    dr.Read();
                    image = (Byte[])(dr["image"]);
                    imageType = Convert.ToString(dr["imageType"]);
                    dr.Close();
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception ex)
            {
                return false;
            }
            finally
            {
                cmd.Connection.Close();
            }

        }

        public static Boolean GetProfileDetails(Int64 profId, ref DataTable profileTable)
        {
            MySqlCommand cmd = new MySqlCommand("GetProfileDetails", new MySqlConnection(GetConnectionString()));
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("p_profId", profId);
            try
            {
                cmd.Connection.Open();
                MySqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                if (dr.HasRows)
                {
                    profileTable.Load(dr);
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception ex)
            {
                return false;
            }
            finally
            {
                cmd.Connection.Close();
            }

        }

        public static Boolean CreateProfileUser(Int64 userId, Int64 parentProfId, Int64 oldRootProfId, String firstName, String lastName, String address, String birthDate, String gender, String isDeath, String deathDt, String socialStatus, String acadimicQual, String country, String city, String blodType, String religion, Byte[] image, String imageType)
        {
            MySqlCommand cmd = new MySqlCommand("CreateProfileUser", new MySqlConnection(GetConnectionString()));
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("p_userId", userId);
            if (parentProfId != -1)
            {
                cmd.Parameters.AddWithValue("p_parentProfId", parentProfId);
            }
            else
            {
                cmd.Parameters.Add("p_parentProfId", MySqlDbType.Int64);
            }
            cmd.Parameters.AddWithValue("p_oldRootProfId", oldRootProfId);
            cmd.Parameters.AddWithValue("p_firstName", firstName);
            cmd.Parameters.AddWithValue("p_lastName", lastName);
            cmd.Parameters.AddWithValue("p_address", address);
            cmd.Parameters.AddWithValue("p_birthDate", birthDate);
            cmd.Parameters.AddWithValue("p_gender", gender);
            cmd.Parameters.AddWithValue("p_isDeath", isDeath);
            cmd.Parameters.AddWithValue("p_deathDt", deathDt);
            cmd.Parameters.AddWithValue("p_socialStatus", socialStatus);
            cmd.Parameters.AddWithValue("p_acadimicQual", acadimicQual);
            cmd.Parameters.AddWithValue("p_country", country);
            cmd.Parameters.AddWithValue("p_city", city);
            cmd.Parameters.AddWithValue("p_blodType", blodType);
            cmd.Parameters.AddWithValue("p_religion", religion);
            cmd.Parameters.AddWithValue("p_image", image);
            cmd.Parameters.AddWithValue("p_imageType", imageType);
            try
            {
                cmd.Connection.Open();
                int rowEff = cmd.ExecuteNonQuery();
                if (rowEff > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception ex)
            {
                return false;
            }
            finally
            {
                cmd.Connection.Close();
            }

        }

        public static Boolean UpdateProfileUser(Int64 profId, String firstName, String lastName, String address, String birthDate, String gender, String isDeath, String deathDt, String socialStatus, String acadimicQual, String country, String city, String blodType, String religion, Byte[] image, String imageType)
        {
            MySqlCommand cmd = new MySqlCommand("UpdateProfileUser", new MySqlConnection(GetConnectionString()));
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("p_profId", profId);
            cmd.Parameters.AddWithValue("p_firstName", firstName);
            cmd.Parameters.AddWithValue("p_lastName", lastName);
            cmd.Parameters.AddWithValue("p_address", address);
            cmd.Parameters.AddWithValue("p_birthDate", birthDate);
            cmd.Parameters.AddWithValue("p_gender", gender);
            cmd.Parameters.AddWithValue("p_isDeath", isDeath);
            cmd.Parameters.AddWithValue("p_deathDt", deathDt);
            cmd.Parameters.AddWithValue("p_socialStatus", socialStatus);
            cmd.Parameters.AddWithValue("p_acadimicQual", acadimicQual);
            cmd.Parameters.AddWithValue("p_country", country);
            cmd.Parameters.AddWithValue("p_city", city);
            cmd.Parameters.AddWithValue("p_blodType", blodType);
            cmd.Parameters.AddWithValue("p_religion", religion);
            cmd.Parameters.AddWithValue("p_image", image);
            cmd.Parameters.AddWithValue("p_imageType", imageType);
            try
            {
                cmd.Connection.Open();
                int rowEff = cmd.ExecuteNonQuery();
                if (rowEff > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception ex)
            {
                return false;
            }
            finally
            {
                cmd.Connection.Close();
            }

        }

        public static Boolean RemoveProfileUser(Int64 profId)
        {
            MySqlCommand cmd = new MySqlCommand("RemoveProfileUser", new MySqlConnection(GetConnectionString()));
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("p_profId", profId);
            try
            {
                cmd.Connection.Open();
                int rowEff = cmd.ExecuteNonQuery();
                if (rowEff > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception ex)
            {
                return false;
            }
            finally
            {
                cmd.Connection.Close();
            }

        }

        public static Boolean GetAllStatisticsUser(Int64 userId, ref DataTable statisticsTable)
        {
            MySqlCommand cmd = new MySqlCommand("GetAllStatisticsUser", new MySqlConnection(GetConnectionString()));
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("p_userId", userId);
            try
            {
                cmd.Connection.Open();
                MySqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

                if (dr.HasRows)
                {
                    statisticsTable.Load(dr);
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception ex)
            {
                return false;
                throw ex;
            }
            finally
            {
                cmd.Connection.Close();
            }
        }

        public static Boolean GetAllTrees(Int64 userId, ref DataTable treesTable)
        {
            MySqlCommand cmd = new MySqlCommand("GetAllTrees", new MySqlConnection(GetConnectionString()));
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("p_userId", userId);
            try
            {
                cmd.Connection.Open();
                MySqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

                if (dr.HasRows)
                {
                    treesTable.Load(dr);
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception ex)
            {
                return false;
            }
            finally
            {
                cmd.Connection.Close();
            }
        }

        public static Boolean GetAllRelationsUser(Int64 userId, ref DataTable relationsTable)
        {
            MySqlCommand cmd = new MySqlCommand("GetAllRelationsUser", new MySqlConnection(GetConnectionString()));
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("p_userId", userId);
            try
            {
                cmd.Connection.Open();
                MySqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

                if (dr.HasRows)
                {
                    relationsTable.Load(dr);
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception ex)
            {
                return false;
            }
            finally
            {
                cmd.Connection.Close();
            }
        }

        public static Boolean AddRelationUser(Int64 userId, Int64 relationUserId)
        {
            MySqlCommand cmd = new MySqlCommand("AddRelationUser", new MySqlConnection(GetConnectionString()));
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("p_userId", userId);
            cmd.Parameters.AddWithValue("p_relationUserId", relationUserId);
            try
            {
                cmd.Connection.Open();
                int rowEff = cmd.ExecuteNonQuery();
                if (rowEff > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception ex)
            {
                return false;
            }
            finally
            {
                cmd.Connection.Close();
            }

        }

        public static Boolean RemoveRelationUser(Int64 relationId)
        {
            MySqlCommand cmd = new MySqlCommand("RemoveRelationUser", new MySqlConnection(GetConnectionString()));
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("p_relationId", relationId);
            try
            {
                cmd.Connection.Open();
                int rowEff = cmd.ExecuteNonQuery();
                if (rowEff > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception ex)
            {
                return false;
            }
            finally
            {
                cmd.Connection.Close();
            }

        }

    }
}